package com.gack.business.dao;

import java.math.BigInteger;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.Query;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.gack.business.model.StoreSupportFacity;
import com.gack.business.model.StoreSupportService;

/**
 * 
* @ClassName: StoreFacityAndServiceNewDao 
* @Description: TODO(新的门店配套设施 配套服务管理dao) 
* @author (ZhangXinYu)  
* @date 2018年9月7日 下午2:29:23 
* @version V1.0
 */
@Repository
public class StoreFacityAndServiceNewDao {
	@Autowired
	private EntityManager entityManager;
	
	/**
	 * 门店配套设施名称是否存在
	 */
	public boolean hasFacity(String id, String name) {
		boolean flag = false;
		String jpql = "select case when(count(id) > 0) then 'true' else 'false' end as hasName from store_support_facity where 1=1 and is_use <> 2"
					+ " and name = :name";
		if(id != null && !id.equals("")){
			jpql += " and id <> :id";
		}
		Query query = entityManager.createNativeQuery(jpql);
		query.setParameter("name", name);
		if(id != null && !id.equals("")){
			query.setParameter("id", id);
		}
		flag = Boolean.valueOf(query.getSingleResult().toString());
		return flag;
	}
	
	/**
	 * 门店配套服务是否存在
	 */
	public boolean hasService(String id, String name) {
		boolean flag = false;
		String jpql = "select case when(count(id) > 0) then 'true' else 'false' end as hasName from store_support_service where 1=1 and is_use <> 2"
					+ " and name = :name";
		if(id != null && !id.equals("")){
			jpql += " and id <> :id";
		}
		Query query = entityManager.createNativeQuery(jpql);
		query.setParameter("name", name);
		if(id != null && !id.equals("")){
			query.setParameter("id", id);
		}
		flag = Boolean.valueOf(query.getSingleResult().toString());
		return flag;
	}
	
	/**
	 * 查询所有未删除的配套设施
	 */
	@SuppressWarnings("unchecked")
	public List<StoreSupportFacity> getAllFacity(){
		List<StoreSupportFacity> facitys = null;
		String jpql = "SELECT * FROM ("
				+ " SELECT"
					+ "	suf.id,"
					+ "	suf.`name`,"
					+ " suf.createtime,"
					+ " suf.is_use,"
					+ " suf.icon,"
					+ " suf.use_template"
				+ " FROM store_support_facity suf"
				+ " WHERE 1=1 AND suf.use_template = 0 AND suf.is_use <> 2"
				+ " UNION"
				+ " SELECT"
					+ " suf2.id,"
					+ " suf2.`name`,"
					+ " suf2.createtime,"
					+ " suf2.is_use,"
					+ " ssfi.icon,"
					+ " suf2.use_template"
				+ " FROM store_support_facity suf2"
				+ " LEFT JOIN store_support_facity_icon ssfi"
				+ " ON suf2.icon = ssfi.id"
				+ " WHERE 1=1 and suf2.use_template = 1 AND suf2.is_use <> 2"
				+ " ) f"
				+ " ORDER BY f.createtime DESC";
		Query query = entityManager.createNativeQuery(jpql,StoreSupportFacity.class);
		facitys = query.getResultList();
		return facitys;
	}
	
	/**
	 * 查询所有未删除的配套服务
	 */
	@SuppressWarnings("unchecked")
	public List<StoreSupportService> getAllService(){
		List<StoreSupportService> services = null;
		String jpql = "SELECT * FROM ("
				+ " SELECT"
					+ " sus.id,"
					+ " sus.`name`,"
					+ " sus.createtime,"
					+ " sus.is_use,"
					+ " sus.icon,"
					+ " sus.use_template"
				+ " FROM store_support_service sus"
				+ " WHERE 1=1 AND sus.use_template = 0 AND sus.is_use <> 2"
				+ " UNION"
				+ " SELECT"
					+ " sus2.id,"
					+ " sus2.`name`,"
					+ " sus2.createtime,"
					+ " sus2.is_use,"
					+ " sssi.icon,"
					+ " sus2.use_template"
				+ " FROM store_support_service sus2"
				+ " LEFT JOIN store_support_service_icon sssi"
				+ " ON sus2.icon = sssi.id"
				+ " WHERE 1=1 and sus2.use_template = 1 AND sus2.is_use <> 2"
				+ " ) s"
				+ " ORDER BY s.createtime DESC";
		Query query = entityManager.createNativeQuery(jpql, StoreSupportService.class);
		services = query.getResultList();
		return services;
	}
	
	/**
	 *  查询列表中已存在的门店配套设施
	 */
	@SuppressWarnings("unchecked")
	public List<String> findExistsFacityName(List<String> names){
		List<String> existsNames = null;
		String jpql = "select"
					+ " ssf.`name`"
				+ " from store_support_facity ssf"
				+ " where 1=1"
					+ " and ssf.is_use <> 2"
					+ " and ssf.`name` in :names";
		Query query = entityManager.createNativeQuery(jpql);
		query.setParameter("names", names);
		existsNames = query.getResultList();
		return existsNames;
	}
	
	/**
	 * 查询列表中已存在的门店配套服务
	 */
	@SuppressWarnings("unchecked")
	public List<String> findExistsServiceName(List<String> names){
		List<String> existsNames = null;
		String jpql = "select"
					+ " sss.`name`"
				+ " from store_support_service sss"
				+ " where 1=1"
					+ " and sss.is_use <> 2"
					+ " and sss.`name` in :names";
		Query query = entityManager.createNativeQuery(jpql);
		query.setParameter("names", names);
		existsNames = query.getResultList();
		return existsNames;
	}
	
	/**
	 * 分页查询门店配套设施
	 */
	@SuppressWarnings("unchecked")
	public List<StoreSupportFacity> searchSupportFacity(int searchType, String keyWord, String startTime, String endTime, int page, int size){
		List<StoreSupportFacity> facitys = null;
		String jpql = "SELECT * FROM ("
				+ " SELECT"
					+ "	suf.id,"
					+ "	suf.`name`,"
					+ " suf.createtime,"
					+ " suf.is_use,"
					+ " suf.icon,"
					+ " suf.use_template"
				+ " FROM store_support_facity suf"
				+ " WHERE 1=1 AND suf.use_template = 0 AND suf.is_use <> 2"
				+ " UNION"
				+ " SELECT"
					+ " suf2.id,"
					+ " suf2.`name`,"
					+ " suf2.createtime,"
					+ " suf2.is_use,"
					+ " ssfi.icon,"
					+ " suf2.use_template"
				+ " FROM store_support_facity suf2"
				+ " LEFT JOIN store_support_facity_icon ssfi"
				+ " ON suf2.icon = ssfi.id"
				+ " WHERE 1=1 and suf2.use_template = 1 AND suf2.is_use <> 2"
				+ " ) f"
				+ " WHERE 1=1";
		if(searchType == 1){
			jpql += " AND f.`name` like '%"+keyWord+"%'";
		}
		jpql += " ORDER BY f.createtime DESC";
		Query query = entityManager.createNativeQuery(jpql,StoreSupportFacity.class);
		facitys = query.setFirstResult(page*size).setMaxResults(size).getResultList();
		return facitys;
	}
	
	/**
	 * 分页查询门店配套设施Total
	 */
	public Long searchSupportFacityTotal(int searchType, String keyWord, String startTime, String endTime){
		String jpql = "SELECT COUNT(*) FROM ("
				+ " SELECT"
					+ "	suf.id,"
					+ "	suf.`name`,"
					+ " suf.createtime,"
					+ " suf.is_use,"
					+ " suf.icon,"
					+ " suf.use_template"
				+ " FROM store_support_facity suf"
				+ " WHERE 1=1 AND suf.use_template = 0 AND suf.is_use <> 2"
				+ " UNION"
				+ " SELECT"
					+ " suf2.id,"
					+ " suf2.`name`,"
					+ " suf2.createtime,"
					+ " suf2.is_use,"
					+ " ssfi.icon,"
					+ " suf2.use_template"
				+ " FROM store_support_facity suf2"
				+ " LEFT JOIN store_support_facity_icon ssfi"
				+ " ON suf2.icon = ssfi.id"
				+ " WHERE 1=1 and suf2.use_template = 1 AND suf2.is_use <> 2"
				+ " ) f"
				+ " WHERE 1=1";
		if(searchType == 1){
			jpql += " AND f.`name` like '%"+keyWord+"%'";
		}
		Query query = entityManager.createNativeQuery(jpql);
		BigInteger total = (BigInteger)query.getSingleResult();
		return total.longValue();
	}
	
	/**
	 * 分页查询门店配套服务
	 */
	@SuppressWarnings("unchecked")
	public List<StoreSupportService> searchSupportService(int searchType, String keyWord, String startTime, String endTime, int page, int size){
		List<StoreSupportService> services = null;
		String jpql = "SELECT * FROM ("
				+ " SELECT"
					+ " sus.id,"
					+ " sus.`name`,"
					+ " sus.createtime,"
					+ " sus.is_use,"
					+ " sus.icon,"
					+ " sus.use_template"
				+ " FROM store_support_service sus"
				+ " WHERE 1=1 AND sus.use_template = 0 AND sus.is_use <> 2"
				+ " UNION"
				+ " SELECT"
					+ " sus2.id,"
					+ " sus2.`name`,"
					+ " sus2.createtime,"
					+ " sus2.is_use,"
					+ " sssi.icon,"
					+ " sus2.use_template"
				+ " FROM store_support_service sus2"
				+ " LEFT JOIN store_support_service_icon sssi"
				+ " ON sus2.icon = sssi.id"
				+ " WHERE 1=1 and sus2.use_template = 1 AND sus2.is_use <> 2"
				+ " ) s"
				+ " WHERE 1=1";
		if(searchType == 1){
			jpql += " AND s.`name` like '%"+keyWord+"%'";
		}
		jpql += " ORDER BY s.createtime DESC";
		Query query = entityManager.createNativeQuery(jpql, StoreSupportService.class);
		services = query.setFirstResult(page*size).setMaxResults(size).getResultList();
		return services;
	}
	
	/**
	 * 分页查询门店配套服务Total
	 */
	public Long searchSupportServiceTotal(int searchType, String keyWord, String startTime, String endTime){
		String jpql = "SELECT COUNT(*) FROM ("
				+ " SELECT"
					+ " sus.id,"
					+ " sus.`name`,"
					+ " sus.createtime,"
					+ " sus.is_use,"
					+ " sus.icon,"
					+ " sus.use_template"
				+ " FROM store_support_service sus"
				+ " WHERE 1=1 AND sus.use_template = 0 AND sus.is_use <> 2"
				+ " UNION"
				+ " SELECT"
					+ " sus2.id,"
					+ " sus2.`name`,"
					+ " sus2.createtime,"
					+ " sus2.is_use,"
					+ " sssi.icon,"
					+ " sus2.use_template"
				+ " FROM store_support_service sus2"
				+ " LEFT JOIN store_support_service_icon sssi"
				+ " ON sus2.icon = sssi.id"
				+ " WHERE 1=1 and sus2.use_template = 1 AND sus2.is_use <> 2"
				+ " ) s"
				+ " WHERE 1=1";
		if(searchType == 1){
			jpql += " AND s.`name` like '%"+keyWord+"%'";
		}
		Query query = entityManager.createNativeQuery(jpql);
		BigInteger total = (BigInteger)query.getSingleResult();
		return total.longValue();
	}
	
}
